import pymysql
from pymysql.cursors import DictCursor
from api.config import Config
from functools import wraps

class DbService:
    """数据库基础服务层"""
    
    @classmethod
    def get_connection(cls):
        """获取数据库连接"""
        return pymysql.connect(
            host=Config.MYSQL_HOST,
            user=Config.MYSQL_USER,
            password=Config.MYSQL_PASSWORD,
            database=Config.MYSQL_DB,
            charset='utf8mb4',
            cursorclass=DictCursor
        )
    
    @classmethod
    def execute_query(cls, query, args=None, fetch=True):
        """执行查询语句"""
        try:
            # 打印 SQL 语句和参数（调试用）
            print("\n[SQL DEBUG]")
            print("Final SQL:", query % args if args else query) # 仅用于调试，不要在生产环境使用

            with cls.get_connection() as conn:
                with conn.cursor() as cursor:
                    cursor.execute(query, args)
                    if fetch:
                        return cursor.fetchall()
                    conn.commit()
                    return cursor.lastrowid if 'INSERT' in query else cursor.rowcount
        except Exception as e:
            print(f"Database error: {e}")
            raise
    
    @classmethod
    def create(cls, table, data):
        """创建记录"""
        columns = ', '.join(data.keys())
        placeholders = ', '.join(['%s'] * len(data))
        query = f"INSERT INTO {table} ({columns}) VALUES ({placeholders})"
        return cls.execute_query(query, tuple(data.values()), fetch=False)
    
    @classmethod
    def get_by_id(cls, table, id):
        """根据ID获取记录"""
        query = f"SELECT * FROM {table} WHERE id = %s"
        result = cls.execute_query(query, (id,))
        return result[0] if result else None
    
    @classmethod
    def get_all(cls, table, where=None, args=None):
        """获取所有记录"""
        query = f"SELECT * FROM {table}"
        if where:
            query += f" WHERE {where}"
        return cls.execute_query(query, args)
    
    @classmethod
    def update(cls, table, id, data):
        """更新记录"""
        set_clause = ', '.join([f"{k} = %s" for k in data.keys()])
        query = f"UPDATE {table} SET {set_clause} WHERE id = %s"
        args = tuple(data.values()) + (id,)
        return cls.execute_query(query, args, fetch=False)
    
    @classmethod
    def delete(cls, table, id):
        """删除记录"""
        query = f"DELETE FROM {table} WHERE id = %s"
        return cls.execute_query(query, (id,), fetch=False)